﻿using Dapper.Library;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq.Expressions;


namespace Dapper.PostgreSql.Library
{
    internal class PostgreSqlProvider : SqlProvider
    {
        /// <summary>
        /// 开始符号
        /// </summary>
        private const char OpenQuote = '"';

        /// <summary>
        /// 结束符号
        /// </summary>
        private const char CloseQuote = '"';

        /// <summary>
        /// 匹配符前缀
        /// </summary>
        private const char ParameterPrefix = '@';

        /// <summary>
        /// 参数信息
        /// </summary>
        private DynamicParameters parameters = null;

        #region SQL拼装信息
        string selectSql = string.Empty;
        int? topNum;
        string fromTableSql = string.Empty;
        string nolockSql = string.Empty;
        WhereExpression whereParams = null;
        string whereSql = string.Empty;
        string orderbySql = string.Empty;
        string limitSql = string.Empty;
        #endregion


        /// <summary>
        /// 分页用到的sql语句
        /// </summary>
        private const string PAGINATION_SQL = @"SELECT	COUNT (1) FROM ({0}) ForMaxCount;--最大条数
                                                SELECT	* FROM ({0}) AS query {1}  OFFSET {2} LIMIT {3};";

        protected sealed override SymbolInfo SymbolInfo { get; set; }

        public PostgreSqlProvider()
        {
            SymbolInfo = new SymbolInfo(OpenQuote, CloseQuote, ParameterPrefix);
            ResolveExpression.InitOption(SymbolInfo);
        }


        /// <summary>
        /// 拼装单条查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public override SqlProvider FormatToSingle<T>()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression);

                fromTableSql = FormatTableName();

                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);

                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

                whereSql = whereParams.SqlCmd;

                Params = whereParams.Param;

                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);

                SqlString = $"{selectSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql} LIMIT 1";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                selectSql = SetContext.SqlString;
                Params = parameters;
                SqlString = $"{selectSql} LIMIT 1";
            }
            return this;
        }

        /// <summary>
        /// 拼装多条查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public override SqlProvider FormatToList<T>()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression);

                fromTableSql = FormatTableName();

                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);

                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

                whereSql = whereParams.SqlCmd;

                Params = whereParams.Param;

                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);

                topNum = SetContext.TopNum;

                limitSql = topNum.HasValue ? " LIMIT " + topNum.Value : "";

                SqlString = $"{selectSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql} {limitSql}";

            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                selectSql = SetContext.SqlString;
                Params = parameters;
                SqlString = $"{selectSql}";
            }
            return this;

        }

        /// <summary>
        /// 拼装分页查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public override SqlProvider FormatToPageList<T>(int pageIndex, int pageSize)
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);
                if (string.IsNullOrEmpty(orderbySql))
                    throw new Exception("order by takes precedence over pagelist");

                selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression);

                fromTableSql = FormatTableName();

                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);

                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

                whereSql = whereParams.SqlCmd;

                Params = whereParams.Param;

                SqlString = $"SELECT COUNT(1) {fromTableSql} {nolockSql} {whereSql};";
                SqlString += $"{selectSql} {fromTableSql} {nolockSql} {whereSql}  {orderbySql} LIMIT {pageSize} OFFSET  {(pageIndex - 1) * pageSize}";
            }
            else
            {

                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);
                if (string.IsNullOrEmpty(orderbySql))
                    throw new Exception("排序方式优先于页列表");

                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                selectSql = SetContext.SqlString;
                Params = parameters;
                //分页查询语句
                SqlString = string.Format(PAGINATION_SQL,
                                            selectSql,
                                            orderbySql,
                                            (pageIndex - 1) * pageSize,
                                            pageSize);
            }

            return this;
        }

        /// <summary>
        /// 获取COUNT()函数SQL
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatCount()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                selectSql = "SELECT COUNT(*)";

                fromTableSql = FormatTableName();

                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);

                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

                whereSql = whereParams.SqlCmd;

                Params = whereParams.Param;

                SqlString = $"{selectSql} {fromTableSql} {nolockSql} {whereSql} ";
            }
            else
            {

                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                selectSql = $"SELECT COUNT(*) FROM ({SetContext.SqlString}) ForCount";
                Params = parameters;
                SqlString = $"{selectSql}";
            }
            return this;

        }

        /// <summary>
        /// 获取Exists()函数SQL
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatExists()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                selectSql = "SELECT 1";

                fromTableSql = FormatTableName();

                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);

                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

                whereSql = whereParams.SqlCmd;

                Params = whereParams.Param;

                SqlString = $"{selectSql} {fromTableSql} {nolockSql} {whereSql} LIMIT 1";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                selectSql = $"SELECT  1 FROM ({SetContext.SqlString}) FormatExists LIMIT 1";
                Params = parameters;
                SqlString = $"{selectSql}";
            }

            return this;
        }

        /// <summary>
        /// 获取DeleteSQL
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatDelete()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                fromTableSql = FormatTableName();

                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

                whereSql = whereParams.SqlCmd;

                Params = whereParams.Param;

                SqlString = $"DELETE {fromTableSql} {whereSql }";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                Params = parameters;
                SqlString = SetContext.SqlString;
            }
            return this;

        }

        /// <summary>
        ///  获取Insert-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public override SqlProvider FormatInsert<T>(T entity)
        {
            var paramsAndValuesSql = FormatInsertParamsAndValues(entity);

            if (SetContext.IfNotExistsExpression == null)
                SqlString = $"INSERT INTO {FormatTableName(false)} ({paramsAndValuesSql[0]}) VALUES({paramsAndValuesSql[1]})";
            else
            {
                var ifnotexistsWhere = ResolveExpression.ResolveWhere(SetContext.IfNotExistsExpression, "INT_");

                SqlString = string.Format(@"INSERT INTO {0}({1})  
                SELECT {2}
                WHERE NOT EXISTS(
                    SELECT 1
                    FROM {0}  
                {3}
                    ); ", FormatTableName(false), paramsAndValuesSql[0], paramsAndValuesSql[1], ifnotexistsWhere.SqlCmd);

                Params.AddDynamicParams(ifnotexistsWhere.Param);
            }

            return this;
        }

        /// <summary>
        ///  获取Update-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateExpression"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdate<T>(Expression<Func<T, T>> updateExpression)
        {
            var update = ResolveExpression.ResolveUpdate(updateExpression);

            var where = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

            var whereSql = where.SqlCmd;

            Params = where.Param;
            Params.AddDynamicParams(update.Param);

            SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";

            return this;
        }

        /// <summary>
        /// 获取Update-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdate<T>(T entity)
        {
            var update = ResolveExpression.ResolveUpdate<T>(a => entity);

            var where = ResolveExpression.ResolveWhere(entity);

            var whereSql = where.SqlCmd;

            Params = where.Param;
            Params.AddDynamicParams(update.Param);

            SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";

            return this;
        }

        /// <summary>
        /// 获取Sum-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="lambdaExpression"></param>
        /// <returns></returns>
        public override SqlProvider FormatSum<T>(LambdaExpression lambdaExpression)
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                selectSql = ResolveExpression.ResolveSum(typeof(T).GetProperties(), lambdaExpression);
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;

                Params = whereParams.Param;
                SqlString = $"{selectSql} {fromTableSql} {nolockSql} {whereSql} ";
            }
            else
            {

                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                Params = parameters;
                selectSql = ResolveExpression.ResolveSum(typeof(T).GetProperties(), lambdaExpression);
                SqlString = $"{selectSql} FROM ({SetContext.SqlString}) FormatSum";
            }



            return this;
        }

        /// <summary>
        /// 获取先Update-再Select-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updator"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdateSelect<T>(Expression<Func<T, T>> updator)
        {
            var keyField = SymbolInfo.CombineFieldName(typeof(T).GetKeyPropertity().GetColumnAttributeName());

            var update = ResolveExpression.ResolveUpdate(updator);

            var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression, false);

            var where = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

            var whereSql = where.SqlCmd;

            Params = where.Param;
            Params.AddDynamicParams(update.Param);

            var topNum = SetContext.TopNum;

            var limitSql = topNum.HasValue ? " LIMIT " + topNum.Value : "";
            var tableName = FormatTableName(false);

            SqlString = $"UPDATE {tableName} {update.SqlCmd} WHERE {keyField} IN (SELECT {keyField} FROM {tableName} {whereSql} {limitSql} FOR UPDATE SKIP LOCKED) RETURNING {selectSql}";

            return this;
        }

        public override SqlProvider ExcuteBulkCopy<T>(IDbConnection conn, IEnumerable<T> list)
        {
            throw new NotImplementedException();
        }
    }
}
